package bancosys.tec.utils.db.converter;

import java.io.File;
import java.io.FileNotFoundException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.HashSet;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Marcio Ribeiro (mmr)
 * @created Apr 25, 2007
 */
public class OracleToMysqlDbConverter extends StatementDbConverter {

    private static final int MAX_INT_FIELD_SIZE = 10;

    private Set<String> numberFields = new TreeSet<String>();

    private Pattern numberFieldPattern = Pattern.compile("\\s*(\\w+)\\s+NUMBER\\s*\\(\\s*(\\d+)\\s*(?:,\\s*0\\s*)?\\).*");

    private Pattern pkPattern =
            Pattern.compile("ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+CONSTRAINT\\s+\\w+\\s+PRIMARY\\s+KEY\\s*\\(\\s*(\\w+)\\s*\\)");

    private Pattern uniqPattern =
            Pattern.compile("ALTER\\s+TABLE\\s+\\w+\\s+ADD\\s+CONSTRAINT\\s+\\w+\\s+UNIQUE\\s*\\(\\s*(\\w+(,\\s*)?)+\\s*\\)");

    private Pattern indexPattern = Pattern.compile("CREATE\\s+INDEX\\s+\\w+\\s+ON\\s+(\\w+)\\s*[(]\\s*(\\w+).*");
    
    private final Pattern alterTablePattern = Pattern.compile("ALTER\\s+TABLE\\s+(\\w+)\\s+.+");
    
    private Set<String> tablesWithAutoIncrement = new HashSet<String>();
    
    /**
     * Construtor.
     * 
     * @param inputFile arquivo de entrada.
     * @param outputFile arquivo de saida.
     * @param dbName nome do banco.
     * @throws FileNotFoundException caso nao encontre arquivo.
     * @throws MalformedURLException url malformada.
     */
    public OracleToMysqlDbConverter(File inputFile, File outputFile, String dbName) throws FileNotFoundException, MalformedURLException {
        super(inputFile, outputFile, dbName);
    }

    /**
     * Construtor.
     * 
     * @param inputFile arquivo de entrada.
     * @param outputFile arquivo de saida.
     * @param dbName nome do banco.
     */
    public OracleToMysqlDbConverter(URL inputFile, File outputFile, String dbName) {
        super(inputFile, outputFile, dbName);
    }

    /**
     * @param st o statement.
     * @return statement convertido.
     */
    @Override
    protected String convertStatement(final String st) {
        String s = st;

        // Ignore
        if (s.startsWith("COMMENT") || s.startsWith("CREATE UNIQUE") || s.startsWith("--") || s.startsWith("CREATE UNIQUE INDEX")
                || s.startsWith("CREATE SEQUENCE")) {
            return null;
        }

        Matcher uniqMatcher = this.uniqPattern.matcher(s);
        if (uniqMatcher.find()) {
            return null;
        }

        Matcher fieldMatcher = this.numberFieldPattern.matcher(s);
        while (fieldMatcher.find()) {
            String coluna = fieldMatcher.group(1);
            int tamanho = Integer.parseInt(fieldMatcher.group(2));

            this.numberFields.add(coluna);
            if (tamanho > MAX_INT_FIELD_SIZE) {
                s =
                        s.replaceAll("(\\s+)" + coluna + "\\s+NUMBER\\s*\\(\\s*" + tamanho + "\\s*(?:,\\s*0\\s*)?\\)", "$1" + coluna
                        + " BIGINT");
            } else {
                s = s.replaceAll("(\\s+)" + coluna + "\\s+NUMBER\\s*\\(\\s*" + tamanho + "\\s*(?:,\\s*0\\s*)?\\)", "$1" + coluna + " INT");
            }
        }

        Matcher pkMatcher = this.pkPattern.matcher(s);
        if (pkMatcher.find()) {
            String tableName = pkMatcher.group(1);
            tablesWithAutoIncrement.add(tableName);
            if (this.numberFields.contains(pkMatcher.group(2))) {
                s =
                        s.replaceAll("ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+CONSTRAINT\\s+\\w+\\s+PRIMARY\\s+KEY\\s*\\(\\s*(\\w+)\\s*\\)", s
                                + this.getNewStatementDelimiterChar() + "\nALTER TABLE $1 MODIFY $2 BIGINT NOT NULL AUTO_INCREMENT UNIQUE"
                                + this.getNewStatementDelimiterChar() + "\n"  + "ALTER TABLE "
                                + tableName + " auto_increment = 100000" + getNewStatementDelimiterChar() + "\n");
            }
        }

        // Add to the end of the statement
        if (s.startsWith("CREATE TABLE")) {
            s = s.replaceAll("\\)$", ") engine=innodb");
        }

        // Replace
        s = s.replaceAll("DATE", "DATETIME");
        s = s.replaceAll("NCHAR", "CHAR");
        s = s.replaceAll("VARCHAR2?\\(([0-9]+)( CHAR)?\\)", "VARCHAR($1)");
        s = s.replaceAll("CHAR?\\(([0-9]+)( CHAR)?\\)", "CHAR($1)");
        s = s.replaceAll("CLOB", "LONGTEXT");
        s = s.replaceAll("BLOB", "LONGBLOB");
        s = s.replaceAll("NUMBER\\((\\d+,\\d+)\\)", "DECIMAL($1)");

        Matcher idx = indexPattern.matcher(s);
        if(idx.matches()) {
            String tableName = idx.group(1);
            if(tablesWithAutoIncrement.contains(tableName)) {
                s = s + getNewStatementDelimiterChar() + "\nALTER TABLE " + tableName + " auto_increment = 100000" + getNewStatementDelimiterChar() + "\n";
            }
        } else {
            idx = alterTablePattern.matcher(s);
            if(idx.matches()) {
                String tableName = idx.group(1);
                if(tablesWithAutoIncrement.contains(tableName)) {
                    s = s + getNewStatementDelimiterChar() + "\nALTER TABLE " + tableName + " auto_increment = 100000" + getNewStatementDelimiterChar() + "\n";
                }
            }
        }
        return s;
    }

    /**
     * @param line a linha.
     * @return <code>true</code> se encontrar delimitador, <code>false</code> se nao.
     */
    @Override
    protected boolean gotDelimiter(String line) {
        return line.matches("^" + this.getOldStatementDelimiterChar() + "$");
    }

    /**
     * @return delimitador antigo.
     */
    @Override
    protected char getOldStatementDelimiterChar() {
        return '/';
    }

    /**
     * @return delimitador novo.
     */
    @Override
    protected char getNewStatementDelimiterChar() {
        return ';';
    }
}